package com.housekeeping.housekeeping.usermanagement.dao.impl;

import com.housekeeping.housekeeping.usermanagement.entity.InformationUser;
import com.housekeeping.housekeeping.usermanagement.utils.DBManager;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;

/*
* 数据库查询
*
* str :用于判断要执行的sql语句
* str1:昵称或手机号
* forward:查询开始时间
* next:查询结束时间
* */
public class SelectDao extends OperationDaoImpl {

    @Override
    public List<InformationUser> select(String str, String str1, String forward, String next)  throws SQLException {
        List<InformationUser> list=new LinkedList<>();
        Connection connection = DBManager.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        InformationUser information = null;
        String sql = null;

        if (str.equals("select")) {
            //获取全部数据
            sql = "select * from order_user";
            preparedStatement = connection.prepareStatement(sql);

        }else if(str.equals("nameSelect")){
            //获取昵称为str1或手机号为str1的数据
            sql="select * from order_user where name like "+"'"+"%"+str1+"%"+"'"+"or phone like "+"'"+"%"+str1+"%"+"'";
            preparedStatement = connection.prepareStatement(sql);

        }else if (str.equals("timeSelect")){
            //将String类型的date转换成Date类型
            Date date1 = Date.valueOf(forward);
            Date date2 = Date.valueOf(next);

            //获取时间大于forward且小于next的数据
            sql="select * from order_user where datetime>=? and datetime<=? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDate(1,date1);
            preparedStatement.setDate(2,date2);
        }

        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String name=resultSet.getString("name");
            String phone=resultSet.getString("phone");
            String wechat=resultSet.getString("wechat");
            Date datetime=resultSet.getDate("datetime");
            SimpleDateFormat f=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            String date = f.format(datetime);
            information=new InformationUser(name,phone,wechat,date);
            list.add(information);
        }
        DBManager.close(connection,preparedStatement);
        return list;
    }

}
